package com.hele.sts.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hele.sts.pub.ConnectDB;
import com.hele.sts.pub.SpringContextHolder;

public class QyhznrDao {
	public static final String TABLE = "bf_qyhznr";
	static Logger logger = LoggerFactory.getLogger(QyhznrDao.class);
	private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

	public static int count(String qymc, String nbxh, String zch, String djjg, String gxdw) {
		Connection conn = null;

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;
		sql = "SELECT count(*) FROM hz_qyhznr WHERE 1=1 ";
		if ((qymc != null) && (qymc.length() > 0)) {
			sql = sql + " AND qymc LIKE ? ";
		}
		if ((nbxh != null) && (nbxh.length() > 0)) {
			sql = sql + " AND nbxh LIKE ? ";
		}
		if ((zch != null) && (zch.length() > 0)) {
			sql = sql + " AND (zch LIKE ? or uniscid like ?)";
		}
		// xuning add 20150210
		if ((djjg != null) && (djjg.length() > 0)) {
			sql = sql + " AND djjg LIKE ? ";
		}
		if ((gxdw != null) && (gxdw.length() > 0)) {
			sql = sql + " AND gxdw LIKE ? ";
		}
		logger.debug(sql);
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			if ((qymc != null) && (qymc.length() > 0)) {
				pstmt.setString(i++, "%" + qymc + "%");
			}
			if ((nbxh != null) && (nbxh.length() > 0)) {
				pstmt.setString(i++, "%" + nbxh + "%");
			}
			if ((zch != null) && (zch.length() > 0)) {
				pstmt.setString(i++, "%" + zch + "%");
				pstmt.setString(i++, "%" + zch + "%");
			}
			if ((djjg != null) && (djjg.length() > 0)) {
				pstmt.setString(i++, djjg + "%");
			}
			if ((gxdw != null) && (gxdw.length() > 0)) {
				pstmt.setString(i++, gxdw + "%");
			}
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}
		return 0;
	}

	public static int countBf(String qymc, String nbxh, String zch, String djjg, String gxdw) {
		Connection conn = null;

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;
		sql = "SELECT count(*) FROM bf_qyhznr WHERE 1=1 and zt in ('D','X','Q') ";
		if ((qymc != null) && (qymc.length() > 0)) {
			sql = sql + " AND qymc LIKE ? ";
		}
		if ((nbxh != null) && (nbxh.length() > 0)) {
			sql = sql + " AND nbxh LIKE ? ";
		}
		if ((zch != null) && (zch.length() > 0)) {
			sql = sql + " AND (zch LIKE ? or uniscid like ?)";
		}
		// xuning add 20150210
		if ((djjg != null) && (djjg.length() > 0)) {
			sql = sql + " AND djjg LIKE ? ";
		}
		// xuning add 20150210
		if ((gxdw != null) && (gxdw.length() > 0)) {
			sql = sql + " AND gxdw LIKE ? ";
		}
		logger.debug(sql);
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			if ((qymc != null) && (qymc.length() > 0)) {
				pstmt.setString(i++, "%" + qymc + "%");
			}
			if ((nbxh != null) && (nbxh.length() > 0)) {
				pstmt.setString(i++, "%" + nbxh + "%");
			}
			if ((zch != null) && (zch.length() > 0)) {
				pstmt.setString(i++, "%" + zch + "%");
				pstmt.setString(i++, "%" + zch + "%");
			}
			if ((djjg != null) && (djjg.length() > 0)) {
				pstmt.setString(i++, djjg + "%");
			}
			if ((gxdw != null) && (gxdw.length() > 0)) {
				pstmt.setString(i++, gxdw + "%");
			}
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}

		return 0;
	}

	public static Qyhznr load(String nbxh) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;
		sql = "SELECT * FROM hz_qyhznr WHERE nbxh = ?  ";

		logger.debug(sql);
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			if ((nbxh != null) && (nbxh.length() > 0)) {
				pstmt.setString(i++, nbxh);
			}
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rsToBean(rs);
			}
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}

		return null;
	}

	public static Qyhznr loadBfTj(String nbxh) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;
		sql = "SELECT * FROM bf_qyhznr WHERE zt in ('D','X','Q') and nbxh = ?  ";

		logger.debug(sql);
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			if ((nbxh != null) && (nbxh.length() > 0)) {
				pstmt.setString(i++, nbxh);
			}
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rsToBean(rs);
			}
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}

		return null;
	}

	public static List load(String qymc, String nbxh, String zch, String djjg, String gxdw, int page, int pageSize) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;
		// sql =
		// "SELECT * FROM (SELECT rownum r, a.*,b.djjgjc as djjgmc,c.content as qyzt FROM hz_qyhznr a,bm_djjg b,bm_qyzt c WHERE a.djjg=b.code and a.zt=c.code ";
		sql = "SELECT * FROM (SELECT rownum r, a.*,nvl(b.djjgjc,'') as djjgmc,nvl(c.content,'') as qyzt FROM hz_qyhznr a,bm_djjg b,bm_qyzt c WHERE a.djjg=b.code(+) and a.zt=c.code(+) ";
		if ((qymc != null) && (qymc.length() > 0)) {
			sql = sql + " AND qymc LIKE ? ";
		}
		if ((nbxh != null) && (nbxh.length() > 0)) {
			sql = sql + " AND nbxh LIKE ? ";
		}
		if ((zch != null) && (zch.length() > 0)) {
			sql = sql + " AND (zch LIKE ? or uniscid like ?) ";
		}
		// xuning add 20150210
		if ((djjg != null) && (djjg.length() > 0)) {
			sql = sql + " AND djjg LIKE ? ";
		}
		if ((gxdw != null) && (gxdw.length() > 0)) {
			sql = sql + " AND gxdw LIKE ? ";
		}
		logger.debug(pageSize + "page" + page);
		if (pageSize > 1) {
			sql = sql + " AND rownum <=? * ?)t WHERE t.r > (? - 1) * ? ";
		}
		logger.info("load sql:" + sql);
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			if ((qymc != null) && (qymc.length() > 0)) {
				pstmt.setString(i++, "%" + qymc + "%");
			}
			if ((nbxh != null) && (nbxh.length() > 0)) {
				pstmt.setString(i++, "%" + nbxh + "%");
			}
			if ((zch != null) && (zch.length() > 0)) {
				pstmt.setString(i++, "%" + zch + "%");
				pstmt.setString(i++, "%" + zch + "%");
			}
			if ((djjg != null) && (djjg.length() > 0)) {
				pstmt.setString(i++, djjg + "%");
			}
			if ((gxdw != null) && (gxdw.length() > 0)) {
				pstmt.setString(i++, gxdw + "%");
			}
			if (pageSize > 1) {
				pstmt.setInt(i++, page);
				pstmt.setInt(i++, pageSize);
				pstmt.setInt(i++, page);
				pstmt.setInt(i++, pageSize);
			}
			rs = pstmt.executeQuery();
			List list = new ArrayList();
			while (rs.next()) {
				list.add(rsToBean1(rs));
			}
			logger.debug(list.size() + "--");
			List localList1 = list;
			return localList1;
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}

		return null;
	}

	public static List loadbf(String qymc, String nbxh, String zch, String djjg, String gxdw, int page, int pageSize) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;
		sql = "SELECT * FROM (SELECT rownum r, a.*,nvl(b.djjgjc,'') as djjgmc,nvl(c.content,'') as qyzt  FROM bf_qyhznr a,bm_djjg b,bm_qyzt c WHERE a.djjg=b.code(+) and a.zt=c.code(+) and zt in ('D','X') ";
		if ((qymc != null) && (qymc.length() > 0)) {
			sql = sql + " AND qymc LIKE ? ";
		}
		if ((nbxh != null) && (nbxh.length() > 0)) {
			sql = sql + " AND nbxh LIKE ? ";
		}
		if ((zch != null) && (zch.length() > 0)) {
			sql = sql + " AND (zch LIKE ? or uniscid like ?) ";
		}
		// xuning add 20150210
		if ((djjg != null) && (djjg.length() > 0)) {
			sql = sql + " AND djjg LIKE ? ";
		}
		if ((gxdw != null) && (gxdw.length() > 0)) {
			sql = sql + " AND gxdw LIKE ? ";
		}
		logger.debug(pageSize + "page" + page);
		if (pageSize > 1) {
			sql = sql + " AND rownum <=? * ?)t WHERE t.r > (? - 1) * ? ";
		}
		logger.info("loadbf sql:" + sql);
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			if ((qymc != null) && (qymc.length() > 0)) {
				pstmt.setString(i++, "%" + qymc + "%");
			}
			if ((nbxh != null) && (nbxh.length() > 0)) {
				pstmt.setString(i++, "%" + nbxh + "%");
			}
			if ((zch != null) && (zch.length() > 0)) {
				pstmt.setString(i++, "%" + zch + "%");
				pstmt.setString(i++, "%" + zch + "%");
			}
			if ((djjg != null) && (djjg.length() > 0)) {
				pstmt.setString(i++, djjg + "%");
			}
			if ((gxdw != null) && (gxdw.length() > 0)) {
				pstmt.setString(i++, gxdw + "%");
			}
			if (pageSize > 1) {
				pstmt.setInt(i++, page);
				pstmt.setInt(i++, pageSize);
				pstmt.setInt(i++, page);
				pstmt.setInt(i++, pageSize);
			}
			rs = pstmt.executeQuery();
			List list = new ArrayList();
			while (rs.next()) {
				list.add(rsToBean1(rs));
			}
			logger.debug(list.size() + "--");
			List localList1 = list;
			return localList1;
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}

		return null;
	}

	static Qyhznr rsToBean(ResultSet rs) throws SQLException {
		Qyhznr bean = new Qyhznr();
		bean.setNbxh(rs.getString("nbxh"));
		bean.setQymc(rs.getString("qymc"));
		bean.setZch(rs.getString("zch"));
		bean.setDjjg(rs.getString("djjg"));
		bean.setQylx(rs.getString("qylx"));

		// 20150203
		bean.setFddbr(rs.getString("fddbr"));
		bean.setQyzt(rs.getString("zt"));

		bean.setZs(rs.getString("zs"));
		bean.setYzbm(rs.getString("yzbm"));
		bean.setDh(rs.getString("dh"));
		bean.setCz(rs.getString("cz"));
		bean.setDzyjdz(rs.getString("dzyjdz"));
		bean.setZczb(rs.getString("zczb"));
		bean.setZczbmy(rs.getString("zczbmy"));
		bean.setCyrs(rs.getString("cyrs"));
		bean.setHy(rs.getString("hy"));
		bean.setJyfw(rs.getString("jyfw"));
		bean.setJyqsrq(rs.getString("jyqsrq"));
		bean.setJyjzrq(rs.getString("jyjzrq"));
		bean.setHzrq(rs.getString("hzrq"));
		bean.setClrq(rs.getString("clrq"));
		bean.setGxdw(rs.getString("gxdw"));

		bean.setUniscid(rs.getString("uniscid"));
		return bean;
	}

	static Qyhznr rsToBean1(ResultSet rs) throws SQLException {
		Qyhznr bean = new Qyhznr();
		bean.setNbxh(rs.getString("nbxh"));
		bean.setQymc(rs.getString("qymc"));
		bean.setZch(rs.getString("zch"));
		bean.setUniscid(rs.getString("uniscid"));
		bean.setDjjg(rs.getString("djjg"));
		bean.setQylx(rs.getString("qylx"));

		bean.setDjjgmc(rs.getString("djjgmc"));
		bean.setFddbr(rs.getString("fddbr"));
		bean.setQyzt(rs.getString("qyzt"));

		return bean;
	}

	public static String getQymc(String zch) {
		String Qymc = null;
		ResultSet rs = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		String sql = "select distinct qymc from hz_qyhznr where zch='" + zch + "'";
		ConnectDB connsyb = null;
		connsyb = SpringContextHolder.getBean(ConnectDB.class);

		try {
			conn = connsyb.getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			if (rs.next()) {
				Qymc = rs.getString(1);
			}
			String str1 = Qymc;
			return str1;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static String getGjjg(String nbxh) {
		String djjg = null;
		ResultSet rs = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		String sql = "select distinct djjg from hz_qyhznr where nbxh like '%" + nbxh + "%'";
		ConnectDB connsyb = null;
		connsyb = SpringContextHolder.getBean(ConnectDB.class);

		String str1 = null;
		try {
			conn = connsyb.getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			if (rs.next()) {
				djjg = rs.getString(1);
			}
			if (djjg == null) {
				sql = "select distinct djjg from bf_qyhznr where nbxh like '%" + nbxh + "%'";
				stmt = conn.prepareStatement(sql);
				rs = stmt.executeQuery();
				if (rs.next()) {
					djjg = rs.getString(1);
				}
				str1 = djjg;
				return str1;
			}
			return str1;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static List find(String sql) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ConnectDB connsyb = null;

		logger.info("load sql:" + sql);
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);

			rs = pstmt.executeQuery();
			List list = new ArrayList();
			while (rs.next()) {
				Map map = new HashMap();
				map.put("rownum", rs.getRow());
				map.put("nbxh", rs.getString("nbxh"));
				map.put("zch", rs.getString("zch"));
				map.put("uniscid", (rs.getString("uniscid") == null) ? "" : rs.getString("uniscid"));
				map.put("qymc", rs.getString("qymc"));
				map.put("qylx", rs.getString("qylx"));
				map.put("djjg", rs.getString("djjg"));
				map.put("fddbr", rs.getString("fddbr"));
				map.put("zs", rs.getString("zs"));
				map.put("content", rs.getString("content"));
				map.put("data_type", rs.getString("data_type"));
				list.add(map);
			}
			logger.debug(list.size() + "--");
			return list;
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}

		return null;
	}

	public static int countBfForImage(String qymc, String nbxh, String zch, String djjg, String gxdw, String startDate,
			String endDate) {
		Connection conn = null;

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;
		sql = "SELECT count(*) FROM bf_qyhznr a WHERE 1=1 and zt in ('D','X','Q') ";
		if ((qymc != null) && (qymc.length() > 0)) {
			sql = sql + " AND qymc LIKE ? ";
		}
		if ((nbxh != null) && (nbxh.length() > 0)) {
			sql = sql + " AND nbxh LIKE ? ";
		}
		if ((zch != null) && (zch.length() > 0)) {
			sql = sql + " AND (zch LIKE ? or uniscid like ?)";
		}
		// xuning add 20150210
		if ((djjg != null) && (djjg.length() > 0)) {
			sql = sql + " AND djjg LIKE ? ";
		}
		// xuning add 20150210
		if ((gxdw != null) && (gxdw.length() > 0)) {
			sql = sql + " AND gxdw LIKE ? ";
		}
		//xuning add 20160922
		if (isValidDate(startDate)||isValidDate(endDate)){
			sql = sql + " AND EXISTS (select 1 from da_qyzfjzmlb b where a.nbxh=b.nbxh ";
			if(startDate!=null){
				sql+=" and b.jnmldjrq >=to_date('"+startDate+"','yyyy-MM-dd')";
			}
			if(endDate!=null){
				sql+=" and b.jnmldjrq <to_date('"+endDate+"','yyyy-MM-dd')+1";
			}
			sql+=")";
		}
		logger.debug(sql);
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			if ((qymc != null) && (qymc.length() > 0)) {
				pstmt.setString(i++, "%" + qymc + "%");
			}
			if ((nbxh != null) && (nbxh.length() > 0)) {
				pstmt.setString(i++, "%" + nbxh + "%");
			}
			if ((zch != null) && (zch.length() > 0)) {
				pstmt.setString(i++, "%" + zch + "%");
				pstmt.setString(i++, "%" + zch + "%");
			}
			if ((djjg != null) && (djjg.length() > 0)) {
				pstmt.setString(i++, djjg + "%");
			}
			if ((gxdw != null) && (gxdw.length() > 0)) {
				pstmt.setString(i++, gxdw + "%");
			}
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}

		return 0;
	}


	public static int countForImage(String qymc, String nbxh, String zch, String djjg, String gxdw, String startDate,
			String endDate) {
		Connection conn = null;

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;
		sql = "SELECT count(*) FROM hz_qyhznr a WHERE 1=1 ";
		if ((qymc != null) && (qymc.length() > 0)) {
			sql = sql + " AND qymc LIKE ? ";
		}
		if ((nbxh != null) && (nbxh.length() > 0)) {
			sql = sql + " AND nbxh LIKE ? ";
		}
		if ((zch != null) && (zch.length() > 0)) {
			sql = sql + " AND (zch LIKE ? or uniscid like ?)";
		}
		// xuning add 20150210
		if ((djjg != null) && (djjg.length() > 0)) {
			sql = sql + " AND djjg LIKE ? ";
		}
		if ((gxdw != null) && (gxdw.length() > 0)) {
			sql = sql + " AND gxdw LIKE ? ";
		}
		//xuning add 20160922
				if (isValidDate(startDate)||isValidDate(endDate)){
					sql = sql + " AND EXISTS (select 1 from da_qyzfjzmlb b where a.nbxh=b.nbxh ";
					if(startDate!=null){
						sql+=" and b.jnmldjrq >=to_date('"+startDate+"','yyyy-MM-dd')";
					}
					if(endDate!=null){
						sql+=" and b.jnmldjrq <to_date('"+endDate+"','yyyy-MM-dd')+1";
					}
					sql+=")";
				}
		logger.debug(sql);
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			if ((qymc != null) && (qymc.length() > 0)) {
				pstmt.setString(i++, "%" + qymc + "%");
			}
			if ((nbxh != null) && (nbxh.length() > 0)) {
				pstmt.setString(i++, "%" + nbxh + "%");
			}
			if ((zch != null) && (zch.length() > 0)) {
				pstmt.setString(i++, "%" + zch + "%");
				pstmt.setString(i++, "%" + zch + "%");
			}
			if ((djjg != null) && (djjg.length() > 0)) {
				pstmt.setString(i++, djjg + "%");
			}
			if ((gxdw != null) && (gxdw.length() > 0)) {
				pstmt.setString(i++, gxdw + "%");
			}
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}
		return 0;
	}

	public static List loadbfForImage(String qymc, String nbxh, String zch, String djjg, String gxdw, String startDate,
			String endDate, int page, int pageSize) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;
		sql = "SELECT * FROM (SELECT rownum r, a.*,nvl(b.djjgjc,'') as djjgmc,nvl(c.content,'') as qyzt  FROM bf_qyhznr a,bm_djjg b,bm_qyzt c WHERE a.djjg=b.code(+) and a.zt=c.code(+) and zt in ('D','X') ";
		if ((qymc != null) && (qymc.length() > 0)) {
			sql = sql + " AND qymc LIKE ? ";
		}
		if ((nbxh != null) && (nbxh.length() > 0)) {
			sql = sql + " AND nbxh LIKE ? ";
		}
		if ((zch != null) && (zch.length() > 0)) {
			sql = sql + " AND (zch LIKE ? or uniscid like ?) ";
		}
		// xuning add 20150210
		if ((djjg != null) && (djjg.length() > 0)) {
			sql = sql + " AND djjg LIKE ? ";
		}
		if ((gxdw != null) && (gxdw.length() > 0)) {
			sql = sql + " AND gxdw LIKE ? ";
		}
		//xuning add 20160922
		if (isValidDate(startDate)||isValidDate(endDate)){
			sql = sql + " AND EXISTS (select 1 from da_qyzfjzmlb b where a.nbxh=b.nbxh ";
			if(startDate!=null){
				sql+=" and b.jnmldjrq >=to_date('"+startDate+"','yyyy-MM-dd')";
			}
			if(endDate!=null){
				sql+=" and b.jnmldjrq <to_date('"+endDate+"','yyyy-MM-dd')+1";
			}
			sql+=")";
		}
		logger.debug(pageSize + "page" + page);
		if (pageSize > 1) {
			sql = sql + " AND rownum <=? * ?)t WHERE t.r > (? - 1) * ? ";
		}
		logger.info("loadbf sql:" + sql);
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			if ((qymc != null) && (qymc.length() > 0)) {
				pstmt.setString(i++, "%" + qymc + "%");
			}
			if ((nbxh != null) && (nbxh.length() > 0)) {
				pstmt.setString(i++, "%" + nbxh + "%");
			}
			if ((zch != null) && (zch.length() > 0)) {
				pstmt.setString(i++, "%" + zch + "%");
				pstmt.setString(i++, "%" + zch + "%");
			}
			if ((djjg != null) && (djjg.length() > 0)) {
				pstmt.setString(i++, djjg + "%");
			}
			if ((gxdw != null) && (gxdw.length() > 0)) {
				pstmt.setString(i++, gxdw + "%");
			}
			if (pageSize > 1) {
				pstmt.setInt(i++, page);
				pstmt.setInt(i++, pageSize);
				pstmt.setInt(i++, page);
				pstmt.setInt(i++, pageSize);
			}
			rs = pstmt.executeQuery();
			List list = new ArrayList();
			while (rs.next()) {
				list.add(rsToBean1(rs));
			}
			logger.debug(list.size() + "--");
			List localList1 = list;
			return localList1;
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}

		return null;
	}

	public static List loadForImage(String qymc, String nbxh, String zch, String djjg, String gxdw, String startDate,
			String endDate, int page, int pageSize) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;
		// sql =
		// "SELECT * FROM (SELECT rownum r, a.*,b.djjgjc as djjgmc,c.content as qyzt FROM hz_qyhznr a,bm_djjg b,bm_qyzt c WHERE a.djjg=b.code and a.zt=c.code ";
		sql = "SELECT * FROM (SELECT rownum r, a.*,nvl(b.djjgjc,'') as djjgmc,nvl(c.content,'') as qyzt FROM hz_qyhznr a,bm_djjg b,bm_qyzt c WHERE a.djjg=b.code(+) and a.zt=c.code(+) ";
		if ((qymc != null) && (qymc.length() > 0)) {
			sql = sql + " AND qymc LIKE ? ";
		}
		if ((nbxh != null) && (nbxh.length() > 0)) {
			sql = sql + " AND nbxh LIKE ? ";
		}
		if ((zch != null) && (zch.length() > 0)) {
			sql = sql + " AND (zch LIKE ? or uniscid like ?) ";
		}
		// xuning add 20150210
		if ((djjg != null) && (djjg.length() > 0)) {
			sql = sql + " AND djjg LIKE ? ";
		}
		if ((gxdw != null) && (gxdw.length() > 0)) {
			sql = sql + " AND gxdw LIKE ? ";
		}
		//xuning add 20160922
		if (isValidDate(startDate)||isValidDate(endDate)){
			sql = sql + " AND EXISTS (select 1 from da_qyzfjzmlb b where a.nbxh=b.nbxh ";
			if(startDate!=null){
				sql+=" and b.jnmldjrq >=to_date('"+startDate+"','yyyy-MM-dd')";
			}
			if(endDate!=null){
				sql+=" and b.jnmldjrq <to_date('"+endDate+"','yyyy-MM-dd')+1";
			}
			sql+=")";
		}
		logger.debug(pageSize + "page" + page);
		if (pageSize > 1) {
			sql = sql + " AND rownum <=? * ?)t WHERE t.r > (? - 1) * ? ";
		}
		logger.info("load sql:" + sql);
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			if ((qymc != null) && (qymc.length() > 0)) {
				pstmt.setString(i++, "%" + qymc + "%");
			}
			if ((nbxh != null) && (nbxh.length() > 0)) {
				pstmt.setString(i++, "%" + nbxh + "%");
			}
			if ((zch != null) && (zch.length() > 0)) {
				pstmt.setString(i++, "%" + zch + "%");
				pstmt.setString(i++, "%" + zch + "%");
			}
			if ((djjg != null) && (djjg.length() > 0)) {
				pstmt.setString(i++, djjg + "%");
			}
			if ((gxdw != null) && (gxdw.length() > 0)) {
				pstmt.setString(i++, gxdw + "%");
			}
			if (pageSize > 1) {
				pstmt.setInt(i++, page);
				pstmt.setInt(i++, pageSize);
				pstmt.setInt(i++, page);
				pstmt.setInt(i++, pageSize);
			}
			rs = pstmt.executeQuery();
			List list = new ArrayList();
			while (rs.next()) {
				list.add(rsToBean1(rs));
			}
			logger.debug(list.size() + "--");
			List localList1 = list;
			return localList1;
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}

		return null;
	}

	private static boolean isValidDate(String date) {
		if(date==null) return false;
		try {
			sdf.parse(date);
			return true;
		} catch (ParseException e) {
			logger.debug(date+" 是无效日期");
		}
		return false;
	}
}
